﻿/*功能描述：系统设置-面试管理+出国考察游
作者：李周蓬
时间：2021-12-23
修改人：李周蓬
修改时间：2021-12-23*/
using Dapper;
using HongStone.Domain.SystemSetupModel.InstitutionSettingModel;
using HongStone.Dto;
using HongStone.Dto.SystemLZPDto;
using Microsoft.Extensions.Caching.Distributed;
using Microsoft.Extensions.Configuration;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HongStone.Repository
{
    public class SystemLZPDal
    {
        private readonly HongStoneContext _hongStoneContext;
        private readonly IConfiguration _configuration;
        private readonly IDistributedCache _cache;
        public SystemLZPDal(HongStoneContext hongStoneContext, IConfiguration configuration, IDistributedCache cache)
        {
            _hongStoneContext = hongStoneContext;
            _configuration = configuration;
            _cache = cache;
        }
        /// <summary>
        /// 面试添加
        /// </summary>
        /// <param name="interview"></param>
        /// <returns></returns>
        public ResultData AddInterview(Interview interview)
        {
            _hongStoneContext.Add(interview);
            return new ResultData() { Code=_hongStoneContext.SaveChanges() };
        }
        /// <summary>
        /// 显示面试信息
        /// </summary>
        /// <param name="deptid"></param>
        /// <param name="name"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        //public ResultData ShowInterview(int deptid, string name, string result)
        //{
        //    List<InterviewDto> list = new List<InterviewDto>();
        //    var show = _cache.GetString("inter");
        //    if(string.IsNullOrWhiteSpace(show))
        //    {
        //        using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
        //        {
        //            var sql = "select a.*,b.DeptName from Interview a join Department b on a.DeptId=b.DeptIdwhere 1=1 ";
        //            list = conn.Query<InterviewDto>(sql).ToList();
        //            _cache.SetString("inter", JsonConvert.SerializeObject(list));
        //            return new ResultData() { Code = 1,Data=list };
        //        }
        //    }
        //    else
        //    {
        //        var item = JsonConvert.DeserializeObject<ResultData>(show).Data; 
        //        return new ResultData() { Code = 1, Data = item };
        //    }
        //}
        public List<InterviewDto> ShowInterview(int deptid, string name, string result)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                List<InterviewDto> list = new List<InterviewDto>();
                var show = _cache.GetString("inter");
                if (string.IsNullOrWhiteSpace(show))
                {
                    var sql = "select a.*,b.DeptName from Interview a join Department b on a.DeptId=b.DeptId where 1=1 ";
                    if(deptid>0)
                    {
                        sql += $" and a.DeptId='{deptid}'";
                    }
                    if(!string.IsNullOrWhiteSpace(name))
                    {
                        sql += $" and a.InterviewName='{name}'";
                    }
                    if (!string.IsNullOrWhiteSpace(result))
                    {
                        sql += $" and a.InterviewResult='{result}'";
                    }
                    list = conn.Query<InterviewDto>(sql).ToList();
                    _cache.SetString("inter", JsonConvert.SerializeObject(list));
                    return list;
                
                }
                else
                {
                    list = JsonConvert.DeserializeObject<List<InterviewDto>>(show).ToList();
                    if (deptid > 0)
                    {
                        list = list.Where(m => m.DeptId == deptid).ToList();
                    }
                    if (!string.IsNullOrWhiteSpace(name))
                    {
                        list = list.Where(m => m.InterviewName == name).ToList();
                    }
                    if (!string.IsNullOrWhiteSpace(result))
                    {
                        list = list.Where(m => m.InterviewResult == result).ToList();
                    }
                    return list;
                }
            }
        }
        /// <summary>
        /// 显示出国旅游资格单
        /// </summary>
        /// <returns></returns>
        public ResultData ShowGo(string year)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = "select a.DictionariesId,a.DictionariesName,b.*,c.StaffId,d.StaffName,b.Time from DictionariesTable a join GoZiGe b on a.DictionariesId=b.DictionariesId join RelationGo c on b.GoZiGeId=c.GoZiGeId join Staff d on c.StaffId=d.StaffId where 1=1 ";
                if(!string.IsNullOrWhiteSpace(year))
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),Time),1,4)='{year}'";
                }
                var list = conn.Query<GoZiGeDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 显示出国旅游安排计算表
        /// </summary>
        /// <returns></returns>
        public ResultData ShowAn(string year)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = "select b.StaffId,c.StaffName,e.*,a.Time,a.Sums from  GoZiGe a join RelationGo b on a.GoZiGeId=b.GoZiGeId join Staff c on b.StaffId=c.StaffId join RelationGos d on b.StaffId=d.StaffId join StaffGoList e on d.StaffGoListId=e.StaffGoListId where 1 =1 ";
                if (!string.IsNullOrWhiteSpace(year))
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),Time),1,4)='{year}'";
                }
                var list = conn.Query<StaffGoListDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 显示出国旅游汇总表
        /// </summary>
        /// <returns></returns>
        public ResultData ShowHui(string year)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = "select a.StaffId,a.StaffName,b.StaffGoListId,c.Year,c.YuE,e.Time from Staff a left join RelationGos b on a.StaffId=b.StaffId left join StaffGoList c on b.StaffGoListId=c.StaffGoListId left join RelationGo d on a.StaffId=d.GoZiGeId left join GoZiGe e on d.StaffId=e.GoZiGeId where 1 =1 ";
                if (!string.IsNullOrWhiteSpace(year))
                {
                    sql += $" and SUBSTRING(CONVERT(nvarchar(20),Time),1,4)='{year}'";
                }
                var list = conn.Query<StaffGoListDto>(sql).ToList();
                return new ResultData() { Code = 1, Data = list };
            }
        }
        /// <summary>
        /// 保存出国旅游安排计算表
        /// </summary>
        /// <returns></returns>
        public ResultData BaoCun(StaffGoList staffGoList)
        {
            using (var conn = new SqlConnection(_configuration.GetConnectionString("MSSQL")))
            {
                var sql = $"update StaffGoList set Xiu={staffGoList.Xiu} where StaffGoListId={staffGoList.StaffGoListId}";

                var list = conn.Execute(sql);
                return new ResultData() { Code = 1 };
            }
        }
    }
}
